const { db, query } = require("../database/db")
// 添加考试记录
// name, type, class_id, date, Number(write_of), 
//         Number(practe_of), Number(meet_of),Number(project_of), all_of, notes
const addtest = async (name, type, class_id, date, write_of, practe_of, meet_of, project_of, all_of, notes) => {
    // 1. 首先创建考试记录
    let sql = `INSERT INTO exams 
                (name, type, class_id, date, write_of, practe_of, meet_of, project_of, all_of, notes) 
                VALUES 
                (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

    const addResults = await query(sql, [name, type, class_id, date, write_of, practe_of, meet_of, project_of, all_of, notes]);

    // 2. 获取刚插入的考试ID
    const examId = addResults.insertId;

    // 3. 查询班级中的所有学生
    const students = await query('SELECT student_id FROM class_students WHERE class_id = ?', [class_id]);

    // 4. 为每个学生创建考试关联记录
    if (students.length > 0) {
        const values = students.map(student => [student.student_id, examId]);
        await query('INSERT INTO student_exams (student_id, exam_id) VALUES ?', [values]);
    }

    return examId;
}
// 删除考试记录--软删除
const deltest_f = (id) => {
    let sql = `
    UPDATE exams SET state_del = 1 WHERE id = ?
    `
    return query(sql, id)
}
const start_state = (id) => {
    let sql = `
    UPDATE exams SET state_del = 0 WHERE id = ?
    `
    return query(sql, id)
}
// 删除考试记录--真删除
const deltest_t = (id) => {
    let sql = `DELETE FROM exams WHERE id = ?`
    return query(sql, id)
}
// 修改考试记录--get数据 && 查询数据
const edit_get = (id) => {
    let sql = `SELECT 
                exams.id,exams.name,exams.type,exams.class_id,exams.date,exams.total_score,
                exams.write_of,exams.practe_of,exams.meet_of,exams.project_of,exams.all_of,exams.notes,
                classes.name as class_name
               FROM exams 
               JOIN classes ON exams.class_id = classes.id
               WHERE exams.id = ? AND exams.state_del = 0`
    return query(sql, id)
}
// 修改考试记录--set数据
const edit_set = (id, name, type, class_id, date, total_score, write_of, practe_of, meet_of, project_of, all_of, notes) => {
    let sql = `UPDATE exams SET 
                name = ?, type = ?, class_id = ?, date = ?, total_score = ?, write_of = ?,
                practe_of = ?, meet_of = ?, project_of = ?, all_of = ?, notes = ?
                WHERE id = ?
    `
    return query(sql, [name, type, class_id, date, total_score, write_of, practe_of, meet_of, project_of, all_of, notes, id])
}
// 考试列表搜索+分页
const test_list = async (name, class_id, program_id, start_date, end_date, type, page, page_size) => {
    // 基础查询（复用相同的JOIN结构）
    const baseSql = `
        SELECT 
            exams.id, exams.name, exams.class_id, classes.program_id, exams.date, exams.type, exams.total_score, exams.average_score, exams.student_count, 
            exams.write_of, exams.practe_of, exams.meet_of, exams.project_of, exams.all_of, exams.state_del,
            classes.name as class_name, programs.name as program_name ,users.truename as teacher_name
        FROM exams
        JOIN classes ON exams.class_id = classes.id
        JOIN programs ON classes.program_id = programs.id
        JOIN users ON exams.class_id = users.class_id
    `;

    // 条件参数
    const conditions = [`0 = 0`];
    const params = [];

    // 动态条件
    if (name) {
        conditions.push(`exams.name LIKE ?`);
        params.push(`%${name}%`);
    }
    if (class_id) {
        conditions.push(`exams.class_id = ?`);
        params.push(class_id);
    }
    if (program_id) {
        conditions.push(`classes.program_id = ?`);
        params.push(program_id);
    }

    if (start_date && end_date) {
        conditions.push(`DATE(exams.date) BETWEEN ? AND ?`);
        params.push(start_date, end_date);
    } else if (start_date) {
        conditions.push(`DATE(exams.date) >= ?`);
        params.push(start_date);
    } else if (end_date) {
        conditions.push(`DATE(exams.date) <= ?`);
        params.push(end_date);
    }

    if (type) {
        conditions.push(`exams.type = ?`);
        params.push(type);
    }

    // 分页参数（仅用于列表查询）
    const paginationParams = [
        page_size,
        (page - 1) * page_size
    ];

    // 列表查询SQL
    const listSql = `
        ${baseSql}
        WHERE ${conditions.join(' AND ')}
        LIMIT ? OFFSET ?
    `;

    // 总数查询SQL
    const countSql = `
        SELECT COUNT(*) as count 
        FROM exams
        JOIN classes ON exams.class_id = classes.id
        JOIN programs ON classes.program_id = programs.id
        WHERE ${conditions.join(' AND ')}
    `;

    // 执行查询（分开传参）
    const [examsResult, countResult] = await Promise.all([
        query(listSql, [...params, ...paginationParams]),
        query(countSql, params) // 总数查询不需要分页参数
    ]);

    // 处理日期格式
    const exams = examsResult.map(item => ({
        ...item,
    }));

    return {
        exams,
        total: countResult[0].count
    };
};

// 每个考试对应的  成绩录入  可以传过来id
// 计算总分的函数
const calculateTotalScore = (write, meet, practe, project, write_of, meet_of, practe_of, project_of) => {
    return (write * (write_of / 100)) + (meet * (meet_of / 100)) + (practe * (practe_of / 100)) + (project * (project_of / 100));
};
// 单个学生的成绩录入
const score_enter = async (id, student_id, score_write, score_meet, score_practe, score_project, comment, callback) => {
    // 查询或更新成绩的SQL语句
    let score_check_sql = `
      SELECT * FROM exam_results WHERE student_id = ? AND exam_id = ?
    `;

    // 根据传过来的id去exams表中查询各个考试的占比
    let score_of_sql = `
      SELECT write_of, practe_of, meet_of, project_of 
      FROM exams WHERE id = ?
    `;

    // 更新成绩的SQL语句
    let score_update_sql = `
      UPDATE exam_results SET 
        score_write = ?, 
        score_meet = ?, 
        score_practe = ?, 
        score_project = ?, 
        score_all = ?, 
        comment = ?
      WHERE student_id = ? AND exam_id = ?
    `;

    // 插入成绩的SQL语句 - 修改为单条插入
    let score_insert_sql = `
      INSERT INTO exam_results (exam_id, student_id, score_write, score_meet, score_practe, score_project, score_all, comment) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;

    try {
        // 首先查询考试占比
        const examProportions = await query(score_of_sql, [id]);
        if (examProportions.length === 0) {
            throw new Error("未找到考试占比信息");
        }
        const { write_of, practe_of, meet_of, project_of } = examProportions[0];

        // 检查学生是否存在
        const student = await query('SELECT id, name, student_id FROM students WHERE id = ?', [student_id]);
        if (student.length === 0) {
            throw new Error("未找到学生信息");
        }

        // 计算总分
        const score_all = calculateTotalScore(
            score_write, score_meet, score_practe, score_project,
            write_of, practe_of, meet_of, project_of
        );

        // 检查exam_results表中是否已经存在成绩记录
        const existingScore = await query(score_check_sql, [student_id, id]);

        if (existingScore.length > 0) {
            // 如果存在，更新成绩
            await query(score_update_sql, [
                score_write, score_meet, score_practe, score_project,
                score_all, comment, student_id, id
            ]);
        } else {
            // 如果不存在，插入新的成绩记录
            await query(score_insert_sql, [
                id, student_id, score_write, score_meet,
                score_practe, score_project, score_all, comment
            ]);
        }

        // 操作完成后，调用 callback 返回结果
        callback(null, { success: true, student_id, exam_id: id });
    } catch (error) {
        callback(error, null);
    }
};

// 统计分析--考试记录
const test_count = async (id, class_id, page, page_size) => {
    let sql = `
        SELECT 
            exam_results.student_id,
            exam_results.score_all, 
            exam_results.score_write, 
            exam_results.score_practe, 
            exam_results.score_meet, 
            exam_results.score_project, 
            exam_results.comment, 
            students.name AS student_name,
            students.student_id AS student_count
        FROM 
            exam_results
        JOIN 
            students ON exam_results.student_id = students.id
        WHERE 
            exam_results.exam_id = ? AND students.class_id = ?
        LIMIT ?
        OFFSET ?
    `;
    let countSql = `
        SELECT COUNT(*) as total_count
        FROM 
            exam_results
        JOIN 
            students ON exam_results.student_id = students.id
        WHERE 
            exam_results.exam_id = ? AND students.class_id = ?
    `;
    const offset = (page - 1) * page_size; // 计算偏移量
    const [results, countResult] = await Promise.all([
        query(sql, [id, class_id, page_size, offset]), // 传入考试ID，页面大小和偏移量
        query(countSql, [id, class_id]) // 只传考试ID
    ])
    // results 是查询结果，countResult 是包含总数的结果
    const total_count = countResult[0].total_count; // 假设返回的结果结构是 { total_count: number }
    return {
        results: results,
        total_count: total_count
    }
}

// 根据班级的id去找这个班级对应的所有学生--成绩录入
const exam_student = async (class_id, exam_id, page, page_size) => {
    const offset = (page - 1) * page_size;

    // 查询班级学生总数
    const countSqlAllStudents = `
        SELECT COUNT(*) as total_count
        FROM students 
        WHERE class_id = ?
    `;

    // 查询已有成绩的学生总数
    const countSqlWithResults = `
        SELECT COUNT(*) as total_count
        FROM students 
        JOIN exam_results ON exam_results.student_id = students.id
        WHERE students.class_id = ? AND exam_results.exam_id = ?
    `;

    // 主查询SQL（有成绩的学生）
    const mainSql = `
        SELECT 
            classes.name,
            classes.id,
            students.name AS student_name,
            students.student_id AS student_count,
            students.id AS student_id,
            exam_results.score_all, 
            exam_results.score_write, 
            exam_results.score_practe, 
            exam_results.score_meet, 
            exam_results.score_project, 
            exam_results.comment 
        FROM classes 
        JOIN students ON students.class_id = classes.id
        LEFT JOIN exam_results ON exam_results.student_id = students.id AND exam_results.exam_id = ?
        WHERE classes.id = ?
        LIMIT ? OFFSET ?
    `;

    try {
        // 获取班级所有学生数和已有成绩学生数
        const [allStudentsCount, studentsWithResultsCount] = await Promise.all([
            query(countSqlAllStudents, [class_id]),
            query(countSqlWithResults, [class_id, exam_id])
        ]);

        const totalStudents = allStudentsCount[0].total_count;
        const studentsWithResults = studentsWithResultsCount[0].total_count;

        // 如果有学生没有成绩记录，则为他们创建空记录
        if (totalStudents > studentsWithResults) {
            // 找出没有成绩记录的学生
            const missingStudentsSql = `
                SELECT students.id as student_id
                FROM students
                WHERE students.class_id = ? 
                AND NOT EXISTS (
                    SELECT 1 FROM exam_results 
                    WHERE exam_results.student_id = students.id 
                    AND exam_results.exam_id = ?
                )
            `;

            const missingStudents = await query(missingStudentsSql, [class_id, exam_id]);

            if (missingStudents.length > 0) {
                // 使用参数化查询批量插入
                const insertSql = `
                    INSERT INTO exam_results (exam_id, student_id)
                    VALUES ?
                `;

                const values = missingStudents.map(student => [exam_id, student.student_id]);
                await query(insertSql, [values]);
            }
        }

        // 执行主查询
        const [results, [totalCount]] = await Promise.all([
            query(mainSql, [exam_id, class_id, page_size, offset]),
            query(countSqlAllStudents, [class_id])
        ]);

        return {
            results: results,
            total_count: totalCount.total_count
        };

    } catch (error) {
        console.error("Error in exam_student:", error);
        throw error; // 或者返回一个错误对象
    }
};
// 统计某个考试的总分
const count_total_item = async (exam_id, class_id) => {
    // 计算总分（SUM而不是COUNT）
    let query_sql = `
        SELECT SUM(score_all) as total_score
        FROM exam_results 
        JOIN 
            students ON exam_results.student_id = students.id
        WHERE exam_id = ? AND score_all IS NOT NULL AND students.class_id = ?
    `;

    const results = await query(query_sql, [exam_id, class_id]);

    // 更新考试表的总分
    let update_sql = `
        UPDATE exams
        SET total_score = ?
        WHERE id = ?
    `;

    // 参数
    const res_update = await query(update_sql, [results[0].total_score || 0, exam_id]);

    return {
        exam_id: exam_id,
        total_score: results[0].total_score || 0,
        updated: res_update.affectedRows > 0
    };
};

// 统计某个考试的平均分
const count_average = async (exam_id, class_id) => {
    // 计算平均分，排除score_all为NULL或0的记录
    let query_sql = `
        SELECT 
            AVG(score_all) as average_score,
            COUNT(score_all) as student_count
        FROM exam_results 
        JOIN 
            students ON exam_results.student_id = students.id
        WHERE exam_id = ? AND score_all IS NOT NULL AND score_all > 0 AND students.class_id = ?
    `;

    const results = await query(query_sql, [exam_id, class_id]);

    // 更新考试表的平均分
    let update_sql = `
        UPDATE exams
        SET average_score = ?, student_count = ?
        WHERE id = ?
    `;

    // 参数
    const res_update = await query(update_sql, [
        results[0].average_score || 0,
        results[0].student_count || 0,
        exam_id
    ]);

    return {
        exam_id: exam_id,
        average_score: results[0].average_score || 0,
        student_count: results[0].student_count || 0,
        updated: res_update.affectedRows > 0
    };
};

// 导出
module.exports = {
    addtest,
    deltest_f,
    deltest_t,
    edit_get,
    edit_set,
    test_list,
    score_enter,
    test_count,
    exam_student,
    start_state,
    count_total_item,
    count_average
}